package com.pacifictextiles.qc.inspection.server.model.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;


import com.pacifictextiles.qc.inspection.server.model.dao.IRollDAO;
import com.pacifictextiles.qc.inspection.server.model.dbc.DatabaseConnection;
import com.pacifictextiles.qc.inspection.server.model.vo.Roll;

public class RollDAOImpl implements IRollDAO {

	/**
	 *在database里增加一条记录
	 *同时return一个数组  记录rollNo 和 indexInBatch
	 */
	
	public String[] addNewRoll(String fullCode, String stationId, String operatorId) throws Exception {
		
		String rollNo = null;
		int indexInBatch = 0;
		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		
		System.out.print("begin to insert a new roll" + "\n");

		// name of the generated Key
		String[] columnNames = { "rollNo" };

		// set sql string to be executed
		// rollNo 自动生成
		// indexInBatch是通过调用 getCountInSameBatch(String) 得到
		String insert ="INSERT INTO `qc_inspection`.`roll` " +
				"( `fullCode`, `stationId`, `operatorId`,`indexInBatch`)" +
				" VALUES (?,?,?,?);";
		
		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(insert, columnNames);
				
			pStatement.setString(1, fullCode);
			pStatement.setString(2, stationId);
			pStatement.setString(3, operatorId);
			// indexInBatch 在同一缸布里自增
			indexInBatch = getindexInBatch(fullCode) + 1;
			pStatement.setInt(4, indexInBatch);
			
			// 执行预处理语句 用 validate 接受返回的 int 判断是否执行成功
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("RollDaoImpl:  添加roll成功" + "\n");
			}

			// 返回ResultSet取得自动生成的key
			ResultSet rs = pStatement.getGeneratedKeys();

			// 取出自动生成的key赋值到String里
			while (rs.next()) {
				rollNo = rs.getString(1);
			}

			// 关闭数据库连接
			dbc.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print("RollDaoImpl:  请检查插入语句是否正确"+"\n");
		}

		//设置一个String[2] 数组返回 rollNo 和 indexInBatch
		String[] backServelet = new String[2];
		backServelet[0] = rollNo;
		backServelet[1] = String.valueOf(indexInBatch);
		System.out.print("rollNo is:"+backServelet[0]+"\n"+"indexInBatch is:"+backServelet[1]+"\n\n");
		
		return backServelet;
	}
	
	public boolean finishUpdateRoll(Roll roll) throws Exception {
		
		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;
		
		System.out.print("Begin to Update"+"\n");
		
		//set sql string to be executed
		//rollNo 自动生成
		String update = "UPDATE `qc_inspection`.`roll` SET `expRollNo` = ? "
				+ ", `defectCount` = ? ,  `qualityLevel` = ? , `cpi` = ? , `wpi` = ?"
				+ ",`space` = ? , `totalPoints` = ?, `width` = ?, `fabricWeight` = ?"
				+ ", `tubeWeight` = ?, `netLength` = ?, `grossLength` = ?, `wasteLength` = ?"
				+ ",`weightingStatus` = ?, `inspectStatus` = ? WHERE `roll`.`rollNo` = '"+roll.getRollNo()+"'";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(update);

			// 准备参数到查询语句中？号的位置从1到N
			pStatement.setString(1, roll.getExpRollNo());
			pStatement.setInt(2, roll.getDefectCount());
			pStatement.setInt(3, roll.getQualityLevel());
			pStatement.setFloat(4, roll.getCpi());
			pStatement.setFloat(5, roll.getWpi());
			pStatement.setFloat(6, roll.getSpace());
			pStatement.setFloat(7, roll.getTotalpoints());
			pStatement.setFloat(8, roll.getWidth());
			pStatement.setFloat(9, roll.getFabricWeight());
			pStatement.setFloat(10, roll.getTubeWeight());
			pStatement.setFloat(11, roll.getNetLength());
			pStatement.setFloat(12, roll.getGrossLength());
			pStatement.setFloat(13, roll.getWasteLength());

			// 此处两个status不是get方法，详情见 Batch类
			pStatement.setBoolean(14, roll.isWeightingStatus());
			pStatement.setBoolean(15, roll.isInspectStatus());

			
			// 执行预处理语句 用 validate 接受返回的 int 判断是否执行成功
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				isSuccess = true;
				System.out.print("RollDaoImpl:  更新roll成功" + "\n\n");
			}else{
				isSuccess = false;
				System.out.print("RollDaoImpl:  找不到roll" + "\n\n");
			}

			// 关闭数据库连接
			dbc.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print("\n"+"RollDaoImpl:  请检查更新语句是否正确"+"\n\n");
		}

		
		return isSuccess;
	}
	
	@Override
	public boolean deleteRoll(String rollNo) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		System.out.print("Begin to delete"+"\n");
		
		String delete = "DELETE FROM `qc_inspection`.`roll` WHERE `roll`.`rollNo` = ?";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(delete);

			// 准备参数到查询语句中？号的位置从1到N
			pStatement.setString(1, rollNo);

			// 执行预处理语句 用 validate 接受返回的 int
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("RollDaoImpl:  删除roll成功" + "\n\n");
				isSuccess = true;
			} else {
				System.out.print("RollDaoImpl:  找不到这一个roll" + "\n\n");
			}

			// 关闭数据库连接
			dbc.close();
			
			//返回 boolean 值判断是否执行成功
			return isSuccess;
			
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.print("RollDaoImpl:  请检查删除语句是否正确"+"\n");
		}
		
		//返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public Roll modifyRoll(Roll roll) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Roll> getRollList(String fullCode) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		List<Roll> rollList = new LinkedList<Roll>();

		String select = "SELECT *  FROM `roll` WHERE `fullCode` = '"+fullCode+"'";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(select);

			// 执行预处理语句
			rs = pStatement.executeQuery(select);
			if (rs != null) {

				// 相当于has next 无下一个return false
				while (rs.next()) {
					Roll roll = new Roll();

					roll.setRollNo(rs.getString("rollNo"));
					roll.setFullCode(rs.getString("fullCode"));
					roll.setExpRollNo(rs.getString("expRollNo"));
					roll.setStationId(rs.getString("stationId"));
					roll.setOperatorId(rs.getString("operatorId"));
					roll.setDefectCount(rs.getInt("defectCount"));
					roll.setQualityLevel(rs.getInt("qualityLevel"));
					roll.setCpi(rs.getFloat("cpi"));
					roll.setWpi(rs.getFloat("wpi"));
					roll.setSpace(rs.getFloat("space"));
					roll.setTotalpoints(rs.getInt("totalPoints"));
					roll.setWidth(rs.getFloat("width"));
					roll.setFabricWeight(rs.getFloat("fabricWeight"));
					roll.setTubeWeight(rs.getFloat("tubeWeight"));
					roll.setNetLength(rs.getFloat("netLength"));
					roll.setGrossLength(rs.getFloat("grossLength"));
					roll.setWasteLength(rs.getFloat("wasteLength"));
					roll.setDateAndTime(rs.getString("dateAndTime"));
					roll.setWeightingStatus(rs.getBoolean("weightingStatus"));
					roll.setInspectStatus(rs.getBoolean("inspectStatus"));

					roll.setIndexInBatch(rs.getInt("indexInBatch"));
					
					rollList.add(roll);
				}

				// 顺利执行后输出提示
				System.out.print("RollDaoImpl:  获取链表成功\n");
				dbc.close();
				//return 后不执行后面的语句
				return rollList;
			}
		} catch (Exception e) {

			// 抓到exception 给出提示
			//e.printStackTrace();
			System.out.print("RollDaoImpl:  获取链表失败\n");
		}

		// result set 返回值为空，给出提示
		System.out.print("RollDaoImpl:  执行查询成功哦，但是查不到结果");
		return null;
	}
	
	public int getindexInBatch(String fullCode) throws Exception {	
		
		//计算出database里 拥有该fullCode的roll 个数
		int countIndexInBatch = 0;
		
		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		
		String select = "SELECT *  FROM `roll` WHERE `fullCode` = '"+fullCode+"'";
		
		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(select);

			// 执行预处理语句
			rs = pStatement.executeQuery(select);

			// 相当于has next 无下一个return false
			while (rs.next()) {
				countIndexInBatch++;
			}
			//关闭数据库连接
			dbc.close();
	
		} catch (Exception e) {

			// e.printStackTrace();
			System.out.print("RollDaoImpl:  计数时执行查询语句失败"+"\n");
		}
		
		return countIndexInBatch;
	}
	

	public boolean updateQualityLevel(String rollNo,int qualityLevel) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String update = "UPDATE `qc_inspection`.`roll` SET  `qualityLevel` = ? WHERE `roll`.`rollNo` = '"
				+ rollNo + "'";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(update);
			pStatement.setInt(1,qualityLevel);
			
			// 执行预处理语句 用 validate 接受返回的 int 判断是否执行成功
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				isSuccess = true;
				System.out.print("RollDaoImpl:  更新quality level： 成功" + "\n\n");
			} else {
				isSuccess = false;
				System.out.print("RollDaoImpl:  更新quality level：  找不到roll" + "\n\n");
			}

		} catch (Exception e) {

			// e.printStackTrace();
			System.out.print("RollDaoImpl:  更新quality level：  执行查询语句失败" + "\n");
		}

		return isSuccess;
	}
	

}
